Class spinitron\dynamicAr\DynamicActiveQuery

Generate DB command from ActiveQuery with Maria-specific SQL for dynamic columns.

User of DynamicActiveQuery should not normally need to use this method.

History ¶

This implementation is the best I could manage. A dynamic attribute name can appear anywhere that a schema attribute name could appear (select, join, where, ...). It needs to be converted to the Maria SQL using COLUMN_CREATE('name', value, …) for accessing dynamic columns. Because SQL is statically-typed and there is no schema to refer to for dynamic attributes, the accessor SQL must specify the the dyn-col's type, e.g.

WHERE COLUMN_GET(details, 'color' AS CHAR) = 'black'

In which details is the blob column containing all the dynamic columns, 'color' is the name of a dynamic column that may or may not appear in any given table record, and CHAR means the value should be cast to CHAR before it is compared with 'black'. COLUMN_GET(details, 'color' AS CHAR) is the "accessor SQL".

So I faced two problems:

1. How to identify a dynamic attribute name in an ActiveQuery?
2. How to choose the type to which it should be cast in the SQL?

The design prociple of DynamicAR is "an attribute that isn't an instance variable, a column and doesn't have a magic get-/setter is assumed to be a dynamic attribute". So, in order to infer from the properties of an AQ instance the attribute names that need to be converted to dynamic column accessor SQL, I need to go through the AQ to identify all the column names and remove those in the schema. But I don't know how to identify column names in an AQ instance. Even if I did, there's problem 2.

The only way I can imagine to infer datatype from an AQ instance is to look at the context. If the attribute is compared with a bound parameter, that's a clue. If it is being used in an SQL function, e.g. CONCAT(), or being compared with a schema column, that suggests something. But if it is on its own in a SELECT then I am stuck. Also stuck if it is compared with another dynamic attribute. This seems fundamentally intractible.

So I decided that the user needs to help DynamicActiveQuery by distinguishing the names of dynamic attributes and by explicitly specifying the type. The format for this:

    (!name|type!)

Omitting type implies the default type: CHAR. Children of dynamic attributes, i.e. array elements, are separated from parents with . (period), e.g. (!address.country|CHAR!). (Spaces are not alowed around the |.) So a user can do:

$blueShirts = Product::find()
    ->where(['category' => Product::SHIRT, '(!color!)' => 'blue'])
    ->all();

$cheapShirts = Product::find()
    ->select(
        ['sale' => 'MAX((!cost|decimal(6,2)!), 0.75 * (!price.wholesale.12|decimal(6,2)!))']
    )
    ->where(['category' => Product::SHIRT])
    ->andWhere('(!price.retail.unit|decimal(6,2)!) < 20.00')
    ->all();

The implementation is like db\Connection's quoting of string and {{string}}. Once the full SQL string is ready, preg_repalce() it. The regex pattern is a bit complex and the replacement callback isn't pretty either. Is there a better way to add to $params in the callback than this? And for the parameter placeholder counter $i?

Returns the value of the element in an array refereced by a dot-notated attribute name.

Converts the indexBy column name an anonymous function that writes rows to the result array indexed an attribute name that may be in dotted notation.

Maria-specific preparation for building a query that includes a dynamic column.